const express = require('express')
const mysql = require('mysql')

const zxTj = express();
zxTj.use(express.json())  /* 请求数据以json格式为主 */
zxTj.use(express.urlencoded({ extended: true })); /* 请求头的数据类型为 x/www-form-urlencoded */

// 配置数据库访问方式
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'k3project'
})

zxTj.get('/zx_can', function (req, res) {
    const zsql = [`SELECT f.c1_name,COUNT(f.c1_name)as tot1 FROM breeding a LEFT JOIN goods b on a.b_id= b.g_way
    LEFT JOIN  purchase c on b.g_id=c.p_item
    LEFT JOIN  class2 d on b.g_class = d.c2_id
    LEFT JOIN  supplier e on b.g_sup = e.s_id
    LEFT JOIN  class1 f on d.class1_id = f.c1_id
    group by f.c1_name`,
        `SELECT d.c2_name,COUNT(d.c2_name)as tot2  FROM breeding a LEFT JOIN goods b on a.b_id= b.g_way
    LEFT JOIN  purchase c on b.g_id=c.p_item
    LEFT JOIN  class2 d on b.g_class = d.c2_id
    LEFT JOIN  supplier e on b.g_sup = e.s_id
    LEFT JOIN  class1 f on d.class1_id = f.c1_id
    group by d.c2_name`
    ]

    db.query(zsql[0], function (err, data) {
        if (err) {
            res.send({ code: 1, err: err })
        } else {
            db.query(zsql[1], function (err2, data2) {
                if (err2) {
                    res.send({ code: 1, err2: err2 })
                } else {
                    res.send({ code: 0, data: [data, data2] })
                }
            })

        }
    })
})
module.exports = zxTj